import { getFileBaseNameOnly } from "@/pages/AuditPaper/PaperTable/components/BatReplace/fileUtils";
import { IColumnProps } from "devextreme-react/data-grid";
import { Alignment, Borders, Cell, Column as ExcelColumn, Workbook, Worksheet } from "exceljs";

import FileSaver from "file-saver";
import { consts } from "../consts";

//const XLSX = require('xlsx-js-style');

import XLSX from 'xlsx-js-style'// 'xlsx';
function getAlignByColumnAlignment(alignment: string): Partial<Alignment> {
    return { horizontal: (alignment ? alignment : "left") as any, vertical: "middle" };
} function getBorder(): Partial<Borders> {
    return {
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" }
    };
}
function truncateSheetName(sheetName: string): string {
    // 计算字符串长度，考虑中文字符
    const length = Array.from(sheetName).length;

    // 如果长度超过31个字符，则截取前31个字符
    if (length > 15) {
        sheetName = Array.from(sheetName).slice(0, 15).join('');
    }

    return sheetName;
}
function getExcelColumns(columns: IColumnProps[]) {
    const excelColumns: Partial<ExcelColumn>[] = columns.filter(column =>
        column.dataField && (column.visible == undefined || column.visible))
        .map(column => {
            let columnWidth = column.width ? column.width / 7.5 : 15;
            let excleColumn: Partial<ExcelColumn> = {
                header: [column.caption],
                key: column.dataField,
                width: columnWidth,
                style: {
                    alignment: getAlignByColumnAlignment(column.alignment),
                    border: getBorder(),
                    numFmt: column.format == consts.numberFormat ? consts.exccelNumberFormat : undefined,
                },

            }
            return excleColumn;
        });
    return excelColumns;
}
/**
 *   导出Excel
 * @param columns 
 * @param rowsData 
 * @param defaultFileNameOnly 
 */
export async function exportDataToExcel(columns: IColumnProps[], rowsData: any[], fileFullName: string) {
    //大于5万行的,用新方式,但没有格式,且右键全部导出还是导出不了,需要找丁肖飞改
    if (rowsData.length > 50000) {
        exportDataToExcelBySheetJs(columns, rowsData, fileFullName)
    } else {
        const excelColumns: Partial<ExcelColumn>[] = getExcelColumns(columns);
        let sheetName = getFileBaseNameOnly(fileFullName).substring(0, 30);//30个汉字

        const workbook = new Workbook();
        const worksheet: Worksheet = workbook.addWorksheet(sheetName);
        // 设置固定第一行
        worksheet.views = [{
            ySplit: 1, // 固定前1行
            state: 'frozen', // 冻结视图状态
        }];
        //数据过滤
        worksheet.autoFilter = {
            from: { row: 1, column: 1 },
            to: { row: 1, column: excelColumns.length }
        };
        worksheet.columns = excelColumns
        //批量加数据(报表行)
        worksheet.addRows(rowsData);

        //标题行加粗
        excelColumns.forEach((column, index) => {
            const cell = worksheet.getCell(1, index + 1);
            cell.font = { bold: true }; // 加粗字体
        });

        const isErrorColumnIndex = worksheet.columns.findIndex(item => item.key == 'isError');
        //错报列加下拉,第2行开始
        if (isErrorColumnIndex > -1) {
            for (let row = 1; row < worksheet.rowCount; row++) {
                const cell = worksheet.getCell(row, isErrorColumnIndex + 1)
                cell.dataValidation = {
                    type: 'list',
                    //excel下拉框
                    formulae: ['"' + consts.AcYesNoOnlyListOfEmpty.map(item => item.id).join(',') + '"'],
                };
            }
        }
        const buffer = await workbook.xlsx.writeBuffer()
        const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
        /* 不生效,不知道传乱原因   
           let localSavePathId = encodeURIComponent(sheetName);
           let localSavePath = encodeURIComponent(fileFullName);
           let outLocalFileName = consts.localBlobFileTag
               + `?localSavePath=${localSavePath}&localSavePathId=${localSavePathId};`
        */
        FileSaver.saveAs(blob, fileFullName);
    }

}
// 定义边框样式
const borderStyle = {
    top: { style: 'thin' },
    bottom: { style: 'thin' },
    left: { style: 'thin' },
    right: { style: 'thin' }
};
/**
 * 通过sheetjs输出
 * 大于5万行的,用新方式,但没有格式,且右键全部导出还是导出不了,需要找丁肖飞改
 * @param columns 
 * @param rowsData 
 * @param fileFullName 
 */
export async function exportDataToExcelBySheetJs(columns: IColumnProps[], rowsData: any[], fileFullName: string) {

    const excelColumns: Partial<ExcelColumn>[] = getExcelColumns(columns);

    //转为xlsx的数据格式
    let ws_data: any[] = [excelColumns.map(column => Array.isArray(column.header) ? column.header[0] : column.header)]//表头

    ws_data = ws_data.concat(rowsData.map(rowData => excelColumns.map(column => rowData[column.key || ''])))

    //生成有单元格样式的,这里如果数据量大了,这个会失效
    const opts: XLSX.AOA2SheetOpts = { cellStyles: true }

    const ws = XLSX.utils.aoa_to_sheet(ws_data);
    //区域
    const range: XLSX.Range = { s: { c: 0, r: 0 }, e: { c: excelColumns.length - 1, r: rowsData.length - 1 } }
    // 设置自动筛选，//'A1:G111' // 指定自动筛选的范围
    ws['!autofilter'] = { ref: XLSX.utils.encode_range(range) };
    //不行ws['!freeze'] = { xSplit: 0, ySplit: 1, topLeftCell: 'A2', activePane: 'bottomLeft' };
    //列宽宽度
    ws['!cols'] = excelColumns.map(column => ({ wch: column.width }));

    // 为指定区域添加边框,这里如果数据量大了,这个会失效
    for (let rowNum = range.s.r; rowNum <= range.e.r; rowNum++) {
        for (let colNum = range.s.c; colNum <= range.e.c; colNum++) {
            const cellAddress = XLSX.utils.encode_cell({ r: rowNum, c: colNum });
            if (!ws[cellAddress]) {
                ws[cellAddress] = { t: 's', v: '' }; // 如果单元格为空，则创建一个空单元格
            }
            const numFmt = excelColumns[colNum]?.style?.numFmt
            ws[cellAddress].s = { border: borderStyle, numFmt };
        }
    }

    var wb = XLSX.utils.book_new();

    let sheetName = getFileBaseNameOnly(fileFullName).substring(0, 30);//30个汉字

    XLSX.utils.book_append_sheet(wb, ws, sheetName);

    XLSX.writeFileXLSX(wb, `${fileFullName}`, opts);
}